package com.junzhiit.nengji.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.CouponsBO;
import com.junzhiit.nengji.bo.UserCouponsBO;
import com.junzhiit.nengji.bo.UserCouponsInfoBO;
import com.junzhiit.nengji.dao.UserCouponsDAO;
import com.junzhiit.nengji.model.UserCoupons;
import com.junzhiit.nengji.model.enums.Is;
import com.junzhiit.nengji.model.enums.Status;


@Repository
public class UserCouponsDAOImpl extends AbstractBaseDAO<UserCoupons> implements UserCouponsDAO {

	@Override
	public List<UserCouponsBO> getCouponsByUserId(String userId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select c.*,uc.id as user_coupons_id,uc.created_time,uc.end_time ");
		sql.append(" from user_coupons uc");
		sql.append(" inner join coupons c on c.id = uc.coupons_id");
		sql.append(" where uc.user_id = ? and uc.`status` = ? and uc.end_time > now()");
		sql.append(" order by uc.created_time desc");
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setInt(Status.ACTIVE.toIntStatus());
		return super.queryForList(sql.toString(), UserCouponsBO.class, params);
	}

	@Override
	public List<UserCouponsBO> getCouponsAll(CouponsBO couponsBO, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select c.*,s.name shopName from coupons c");
		sql.append(" inner join shop s on c.shop_id = s.id where c.status = ? ");
		StatementParameter params = new StatementParameter();
		
		params.setInt(Status.INACTIVE.toIntStatus());
		if(couponsBO.getIsChain()!=null){
			if( couponsBO.getIsChain() == Is.NO.toIntValue() ){
				if(StringUtils.isNotBlank(couponsBO.getShopId())){
					sql.append(" and c.shop_id = ? ");
					params.setString(couponsBO.getShopId());
				}
			}else{
				if(StringUtils.isNotBlank(couponsBO.getChainId())){
					sql.append(" and c.chain_id = ? ");
					params.setString(couponsBO.getChainId());
				}
			}
		}else{
			if(StringUtils.isNotBlank(couponsBO.getShopId())){
				sql.append(" and c.shop_id = ?");
				params.setString(couponsBO.getShopId());
			}
		}
		
		if (StringUtils.isNotBlank(couponsBO.getName())) {
			sql.append(" and c.name like ?");
			params.setString("%" + couponsBO.getName() + "%");
		}

		return super.queryForPagingList(sql.toString(), UserCouponsBO.class,params, pageNumber, maxPageRow);
	}

	@Override
	public int getCouponsCount(CouponsBO couponsBO) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(id) from coupons c where c.status = ? ");

		StatementParameter param = new StatementParameter();
		param.setInt(Status.INACTIVE.toIntStatus());
		if(couponsBO.getIsChain()!=null){
			if( couponsBO.getIsChain() == Is.NO.toIntValue() ){
				if(StringUtils.isNotBlank(couponsBO.getShopId())){
					sql.append(" and c.shop_id = ? ");
					param.setString(couponsBO.getShopId());
				}
			}else{
				if(StringUtils.isNotBlank(couponsBO.getChainId())){
					sql.append(" and c.chain_id = ? ");
					param.setString(couponsBO.getChainId());
				}
			}
		}else{
			if(StringUtils.isNotBlank(couponsBO.getShopId())){
				sql.append(" and c.shop_id = ?");
				param.setString(couponsBO.getShopId());
			}
		}

		if (StringUtils.isNotBlank(couponsBO.getName())) {
			sql.append(" and c.name like ?");
			param.setString("%" + couponsBO.getName() + "%");
		}

		return queryForInt(sql.toString(), param);
	}

	@Override
	public List<UserCoupons> getCouponsById() {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from user_coupons where status = ? and is_delete = ?");
		StatementParameter param = new StatementParameter();
		param.setInt(Status.ACTIVE.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql.toString(), UserCoupons.class, param);
	}

	@Override
	public List<UserCouponsInfoBO> getCouponsByUserIdAndShopId(String userid, String findInShopIdSet,String findInChainIdSet) {
		StringBuffer sql = new StringBuffer();
		sql.append("select uc.*,s.name shopName");
		sql.append(" from user_coupons uc");
		sql.append(" left join shop s on uc.shop_id = s.id  ");
		sql.append(" where uc.user_id = ? and uc.`status` = ? and ( uc.order_id is null or uc.order_id ='') and (uc.order_number is null or uc.order_number ='') and uc.start_time<= now() and uc.end_time > now()  ");
		sql.append(" and  (( find_in_set(uc.shop_id,?) >0 or find_in_set(uc.chain_id,?)>0)  or ( uc.shop_id is null and uc.chain_id is null )) ");
		sql.append(" order by uc.created_time desc ");
		StatementParameter params = new StatementParameter();
		params.setString(userid);
		params.setInt(Status.ACTIVE.toIntStatus());
		params.setString(findInShopIdSet);
		params.setString(findInChainIdSet);
		List<UserCouponsInfoBO> list = super.queryForList(sql.toString(), UserCouponsInfoBO.class, params);
		return list;
	}
	
	@Override
	public List<UserCouponsInfoBO> getCouponsByShopId(String userid, String csId,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select uc.*,s.name shopName");
		sql.append(" from user_coupons uc");
		sql.append(" left join shop s on uc.shop_id = s.id  ");
		sql.append(" where uc.user_id = ? and uc.`status` = ? and ( uc.order_id is null or uc.order_id ='') and (uc.order_number is null or uc.order_number ='') and uc.start_time<= now() and uc.end_time > now() ");
		StatementParameter params = new StatementParameter();
		params.setString(userid);
		params.setInt(Status.ACTIVE.toIntStatus());
		
		if(StringUtils.isNotBlank(csId) || StringUtils.isNotBlank(shopId)){
			sql.append(" and (uc.shop_id = ? or uc.chain_id = ?) ");
			params.setString(shopId);
			params.setString(csId);
		}
		sql.append(" order by uc.created_time desc ");
		List<UserCouponsInfoBO> list = super.queryForList(sql.toString(), UserCouponsInfoBO.class, params);
		return list;
	}

	@Override
	public UserCoupons getUserCouponsByCouponsId(String userId,String couponsId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append(" select * from user_coupons ");
		sql.append(" where coupons_id = ? and user_id = ? and is_delete = ? and status = ? ");
		sql.append(" and ( order_id is null or order_id ='') and (order_number is null or order_number ='') ");
		sql.append(" and start_time<= now() and end_time > now() ");
		params.setString(couponsId);
		params.setString(userId);
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(Status.ACTIVE.toIntStatus());
		return super.query(sql.toString(), UserCoupons.class, params);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean updateUserCoupons(List<String> list) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("update user_coupons set order_id = null,order_number = null,status=?  where order_id in (");
		params.setInt(Status.ACTIVE.toIntStatus());
		for(String s:list){
			sql.append("?,");
			params.setString(s);
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		return super.updateForBoolean(sql.toString(), params);
	}
	
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean updateUserCoupons(String ordersId) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("update user_coupons set order_id = null,order_number = null,status=?  where order_id = ? ");
		params.setInt(Status.ACTIVE.toIntStatus());
		params.setString(ordersId);
		return super.updateForBoolean(sql.toString(), params);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public boolean updateUserCouponsStatus(String couponsId,Integer status) {
		StringBuffer sql = new StringBuffer();
		StatementParameter params = new StatementParameter();
		sql.append("update user_coupons set status= ? where coupons_id = ? and is_delete = ? ");
		params.setInt(status);
		params.setString(couponsId);
		params.setInt(Is.NO.toIntValue());
		return super.updateForBoolean(sql.toString(), params);
	}

	@Override
	public UserCoupons getUserCoupons(String userId, String couponsId, String orderNumber) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from user_coupons where user_id = ? and (order_id =? or order_number =?)");
		StatementParameter params = new StatementParameter();
		params.setString(userId);
		params.setString(couponsId);
		params.setString(orderNumber);
		return super.query(sql.toString(),UserCoupons.class,params);
	}
}
